package cn.lili.modules.wallet.mapper;

import cn.lili.common.vo.PageVO;
import cn.lili.modules.order.order.entity.vo.OrderVO;
import cn.lili.modules.wallet.entity.dos.WalletDetail;
import cn.lili.modules.wallet.entity.vo.WalletDetailVO;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * create by yudan on 2022/3/31
 */
public interface WalletDetailMapper extends BaseMapper<WalletDetail> {

	// 查询支出明细
	@Select("SELECT d.*,m.location,m.mobile from li_wallet_detail d" + " LEFT JOIN li_member m ON m.id = d.payee "
			+ " ${ew.customSqlSegment} order by d.create_time desc")
	IPage<WalletDetailVO> getOutlayList(IPage<WalletDetailVO> page,
			@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper);

	// 查询收入明细
	@Select("SELECT d.*,m.location,m.mobile from li_wallet_detail d" + " LEFT JOIN li_member m ON m.id = d.payer "
			+ " ${ew.customSqlSegment} order by d.create_time desc")
	IPage<WalletDetailVO> getIncomeList(IPage<WalletDetailVO> page,
			@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper);

	// 查询明细
	@Select("SELECT d.*,s.store_name,o.commission from li_wallet_detail d" + " left join li_order o on o.sn = d.sn"
			+ " left join li_store s on s.id = o.store_id" + " ${ew.customSqlSegment} order by d.create_time desc")
	IPage<WalletDetailVO> getList(IPage<WalletDetailVO> page,
			@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper);

	// 查询合伙人年收益
	@Select("select *,ROUND(SUM(money),5) as payeeMoney "
			+ " FROM ( SELECT DATE_FORMAT(d.create_time,'%Y-%m') time,d.money "
			+ " FROM li_wallet_detail d LEFT JOIN li_transfer t on t.sn = d.sn "
			+ " WHERE d.payee = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_FW','WALLET_COMMISSION_SY','WALLET_COMMISSION_TS','WALLET_COMMISSION_HY','WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND','WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) "
			+ " UNION all " + " SELECT DATE_FORMAT(d.create_time,'%Y-%m') time,-d.money as money "
			+ " FROM li_wallet_detail d " + " LEFT JOIN li_transfer t on t.sn = d.sn "
			+ " WHERE d.payer = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_SY','WALLET_COMMISSION_TS','WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND','WALLET_COMMISSION_FW_REFUND','WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' )"
			+ " AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) )as a GROUP BY DATE_FORMAT(time,'%Y-%m') desc ")
	IPage<WalletDetailVO> findYear(IPage<WalletDetailVO> page, String memberId);

	// 查询合伙人月收益
	@Select("select " + " *,ROUND(SUM(money),5) as payeeMoney from ( "
			+ "SELECT d.money,s.store_name,s.store_phone,s.store_logo,( case "
			+ " when not ISNULL(m.nick_name) then m.nick_name " + " when not ISNULL(mm.nick_name) then mm.nick_name "
			+ " when not ISNULL(mmt.nick_name) then mmt.nick_name " + " end ) as nickName, "
			+ " d.transaction_type,( case " + " when not ISNULL(mc.`name`) then mc.`name` "
			+ " when not ISNULL(mcc.`name`) then mcc.`name` " + " when not ISNULL(mcct.name) then mcct.name "
			+ " end ) as cardName,LEFT ( d.sn, 1 ) AS STATUS, " + " ( case " + " when not ISNULL(m.face) then m.face "
			+ " when not ISNULL(mm.face) then mm.face " + " when not ISNULL(mmt.face) then mmt.face "
			+ " end ) as face,( case " + " when not ISNULL(m.mobile) then m.mobile "
			+ " when not ISNULL(mm.mobile) then mm.mobile " + " when not ISNULL(mmt.mobile) then mmt.mobile "
			+ " end ) as mobile, " + " ( case " + " when not ISNULL(r.recharge_money) then r.recharge_money "
			+ " when not ISNULL(o.flow_price) then o.flow_price "
			+ " when not ISNULL(t.transfer_money) then t.transfer_money "
			+ " end ) as payerMoney,d.create_time,d.sn,d.payer_name,d.payee_name " + " FROM li_wallet_detail d "
			+ " LEFT JOIN li_recharge r ON r.recharge_sn = d.sn " + " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " LEFT JOIN li_member m ON m.id = r.member_id " + " LEFT JOIN li_order o ON o.sn = d.sn "
			+ " LEFT JOIN li_member mm ON mm.id = o.member_id " + " LEFT JOIN li_member mmt ON mmt.id = t.payer "
			+ " LEFT JOIN li_store s ON s.id = o.store_id "
			+ " LEFT JOIN li_grade_level g ON g.member_id = o.member_id AND g.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level gg ON gg.member_id = r.member_id AND gg.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level ggt ON ggt.member_id =t.payer AND ggt.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card mc ON mc.id = g.grade_id "
			+ " LEFT JOIN li_membership_card mcc ON mcc.id = gg.grade_id "
			+ " LEFT JOIN li_membership_card mcct ON mcct.id = ggt.grade_id " + "WHERE d.payee = #{memberId} "
			+ "  AND d.transaction_type IN ( 'WALLET_COMMISSION_FW','WALLET_COMMISSION_SY','WALLET_COMMISSION_TS','WALLET_COMMISSION_HY','WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND','WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "  AND (( " + "   t.type <> 'PORT_FEE' " + "   ) " + "  OR ( t.type IS NULL )) "
			+ "  AND date_format( d.create_time, '%Y-%m' ) = #{format} " + " union all "
			+ "   SELECT -d.money as money,s.store_name,s.store_phone,s.store_logo, " + " ( case "
			+ " when not ISNULL(m.nick_name) then m.nick_name " + " when not ISNULL(mm.nick_name) then mm.nick_name "
			+ " when not ISNULL(mmt.nick_name) then mmt.nick_name " + " end ) as nickName, "
			+ " d.transaction_type,( case " + " when not ISNULL(mc.`name`) then mc.`name` "
			+ " when not ISNULL(mcc.`name`) then mcc.`name` " + " when not ISNULL(mcct.name) then mcct.name "
			+ " end ) as cardName," + "LEFT ( d.sn, 1 ) AS STATUS," + "( case "
			+ " when not ISNULL(m.face) then m.face " + " when not ISNULL(mm.face) then mm.face "
			+ " when not ISNULL(mmt.face) then mmt.face " + " end ) as face," + "( case "
			+ " when not ISNULL(m.mobile) then m.mobile " + " when not ISNULL(mm.mobile) then mm.mobile "
			+ " when not ISNULL(mmt.mobile) then mmt.mobile " + " end ) as mobile, " + " ( case "
			+ " when not ISNULL(r.recharge_money) then r.recharge_money "
			+ " when not ISNULL(o.flow_price) then o.flow_price "
			+ " when not ISNULL(t.transfer_money) then t.transfer_money " + " end ) as payerMoney, "
			+ "d.create_time,d.sn,d.payer_name,d.payee_name " + "FROM " + " li_wallet_detail d "
			+ " LEFT JOIN li_recharge r ON r.recharge_sn = d.sn " + " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " LEFT JOIN li_member m ON m.id = r.member_id " + " LEFT JOIN li_order o ON o.sn = d.sn "
			+ " LEFT JOIN li_member mm ON mm.id = o.member_id " + " LEFT JOIN li_member mmt ON mmt.id = t.payer "
			+ " LEFT JOIN li_store s ON s.id = o.store_id "
			+ " LEFT JOIN li_grade_level g ON g.member_id = o.member_id " + " AND g.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level gg ON gg.member_id = r.member_id " + " AND gg.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level ggt ON ggt.member_id =t.payer AND ggt.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card mc ON mc.id = g.grade_id "
			+ " LEFT JOIN li_membership_card mcc ON mcc.id = gg.grade_id "
			+ " LEFT JOIN li_membership_card mcct ON mcct.id = ggt.grade_id " + "WHERE " + " d.payer = #{memberId} "
			+ "  AND d.transaction_type IN ( 'WALLET_COMMISSION_SY','WALLET_COMMISSION_TS','WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND','WALLET_COMMISSION_FW_REFUND','WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "  AND (( " + "   t.type <> 'PORT_FEE' " + "   ) " + "  OR ( t.type IS NULL )) "
			+ "  AND date_format( d.create_time, '%Y-%m' ) = #{format} " + "   "
			+ ")as a GROUP BY sn HAVING payeeMoney != 0 ORDER BY create_time DESC ")
	IPage<WalletDetailVO> findMonth(IPage<WalletDetailVO> page, @Param("memberId") String memberId,
			@Param("format") String format);
	// @Select("SELECT s.store_name,s.store_phone,ifNull( m.nick_name, mm.nick_name
	// ) nickName,sum(d.money) AS
	// payeeMoney,d.transaction_type,ifNull( mc.`name`, mcc.`name` ) AS
	// cardName,d.create_time,LEFT ( d.sn, 1 ) AS
	// status,d.sn, " +
	// " ifNull( m.face, mm.face ) AS face,ifNull( m.mobile,mm.mobile ) AS
	// mobile,IFNULL( r.recharge_money, o.flow_price
	// ) AS payerMoney " +
	// "FROM li_wallet_detail d " +
	// "LEFT JOIN li_recharge r ON r.recharge_sn = d.sn " +
	// "LEFT JOIN li_transfer t on t.sn = d.sn " +
	// "LEFT JOIN li_member m ON m.id = r.member_id " +
	// " LEFT JOIN li_order o ON o.sn = d.sn " +
	// " LEFT JOIN li_member mm on mm.id = o.member_id " +
	// "LEFT JOIN li_store s ON s.id = o.store_id " +
	// "LEFT JOIN li_grade_level g ON g.member_id = o.member_id AND g.delete_flag =
	// 0 " +
	// "LEFT JOIN li_grade_level gg ON gg.member_id = r.member_id AND gg.delete_flag
	// = 0 " +
	// "LEFT JOIN li_membership_card mc ON mc.id = g.grade_id " +
	// "LEFT JOIN li_membership_card mcc ON mcc.id = gg.grade_id AND mc.`level` IN (
	// 1, 2 ) ${ew.customSqlSegment}")
	// IPage<WalletDetailVO> findMonth(IPage<WalletDetailVO> page,
	// @Param("memberId") String memberId,@Param("format")
	// String format);

	// 查询合伙人日收益
	@Select(" select *,ROUND(SUM(money),5) as payeeMoney "
			+ " FROM ( SELECT d.money,s.store_name,s.store_phone,s.store_logo,( case when not ISNULL(m.nick_name) then m.nick_name when not ISNULL(mm.nick_name) then mm.nick_name when not ISNULL(mmt.nick_name) then mmt.nick_name end ) as nickName, d.transaction_type,( case when not ISNULL(mc.`name`) then mc.`name` when not ISNULL(mcc.`name`) then mcc.`name` when not ISNULL(mcct.name) then mcct.name end ) as cardName,LEFT ( d.sn, 1 ) AS STATUS, ( case when not ISNULL(m.face) then m.face when not ISNULL(mm.face) then mm.face when not ISNULL(mmt.face) then mmt.face end ) as face,( case when not ISNULL(m.mobile) then m.mobile when not ISNULL(mm.mobile) then mm.mobile when not ISNULL(mmt.mobile) then mmt.mobile end ) as mobile, ( case when not ISNULL(r.recharge_money) then r.recharge_money when not ISNULL(o.flow_price) then o.flow_price when not ISNULL(t.transfer_money) then t.transfer_money end ) as payerMoney,d.create_time,d.sn,d.payer_name,d.payee_name "
			+ " FROM li_wallet_detail d LEFT JOIN li_recharge r " + " ON r.recharge_sn = d.sn LEFT JOIN li_transfer t "
			+ " ON t.sn = d.sn LEFT JOIN li_member m " + " ON m.id = r.member_id LEFT JOIN li_order o "
			+ " ON o.sn = d.sn LEFT JOIN li_member mm " + " ON mm.id = o.member_id LEFT JOIN li_member mmt "
			+ " ON mmt.id = t.payer LEFT JOIN li_store s " + " ON s.id = o.store_id LEFT JOIN li_grade_level g "
			+ " ON g.member_id = o.member_id AND g.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level gg ON gg.member_id = r.member_id AND gg.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level ggt ON ggt.member_id =t.payer AND ggt.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card mc ON mc.id = g.grade_id "
			+ " LEFT JOIN li_membership_card mcc ON mcc.id = gg.grade_id "
			+ " LEFT JOIN li_membership_card mcct ON mcct.id = ggt.grade_id "
			+ " WHERE d.payee = #{memberId} AND d.transaction_type IN"
			+ " ( 'WALLET_COMMISSION_FW','WALLET_COMMISSION_SY','WALLET_COMMISSION_TS','WALLET_COMMISSION_HY','WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND','WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) AND date_format( d.create_time, '%Y-%m-%d' ) = #{format} "
			+ " UNION all "
			+ " SELECT -d.money as money,s.store_name,s.store_phone,s.store_logo, ( case when not ISNULL(m.nick_name) then m.nick_name when not ISNULL(mm.nick_name) then mm.nick_name when not ISNULL(mmt.nick_name) then mmt.nick_name end ) as nickName, d.transaction_type,( case when not ISNULL(mc.`name`) then mc.`name` when not ISNULL(mcc.`name`) then mcc.`name` when not ISNULL(mcct.name) then mcct.name end ) as cardName,LEFT ( d.sn, 1 ) AS STATUS,( case when not ISNULL(m.face) then m.face when not ISNULL(mm.face) then mm.face when not ISNULL(mmt.face) then mmt.face end ) as face,( case when not ISNULL(m.mobile) then m.mobile when not ISNULL(mm.mobile) then mm.mobile when not ISNULL(mmt.mobile) then mmt.mobile end ) as mobile, ( case when not ISNULL(r.recharge_money) then r.recharge_money when not ISNULL(o.flow_price) then o.flow_price when not ISNULL(t.transfer_money) then t.transfer_money end ) as payerMoney, d.create_time,d.sn,d.payer_name,d.payee_name "
			+ " FROM li_wallet_detail d " + " LEFT JOIN li_recharge r ON r.recharge_sn = d.sn "
			+ " LEFT JOIN li_transfer t ON t.sn = d.sn " + " LEFT JOIN li_member m ON m.id = r.member_id "
			+ " LEFT JOIN li_order o ON o.sn = d.sn " + " LEFT JOIN li_member mm ON mm.id = o.member_id "
			+ " LEFT JOIN li_member mmt ON mmt.id = t.payer " + " LEFT JOIN li_store s ON s.id = o.store_id "
			+ " LEFT JOIN li_grade_level g ON g.member_id = o.member_id AND g.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level gg ON gg.member_id = r.member_id AND gg.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level ggt ON ggt.member_id =t.payer AND ggt.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card mc ON mc.id = g.grade_id "
			+ " LEFT JOIN li_membership_card mcc ON mcc.id = gg.grade_id "
			+ " LEFT JOIN li_membership_card mcct ON mcct.id = ggt.grade_id "
			+ " WHERE d.payer = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_SY','WALLET_COMMISSION_TS','WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND','WALLET_COMMISSION_FW_REFUND','WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' )"
			+ " AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) AND date_format( d.create_time, '%Y-%m-%d' ) = #{format} )as a GROUP BY sn HAVING payeeMoney != 0 ORDER BY create_time DESC")
	IPage<WalletDetailVO> findDay(IPage<WalletDetailVO> page, @Param("memberId") String memberId,
			@Param("format") String format);
	// @Select("SELECT s.store_name,s.store_phone,ifNull( m.nick_name, mm.nick_name
	// ) nickName,sum(d.money) AS
	// payeeMoney,d.transaction_type,ifNull( mc.`name`, mcc.`name` ) AS
	// cardName,d.create_time,LEFT ( d.sn, 1 ) AS
	// status,d.sn, " +
	// " ifNull( m.face, mm.face ) AS face,ifNull( m.mobile,mm.mobile ) AS
	// mobile,IFNULL( r.recharge_money, o.flow_price
	// ) AS payerMoney " +
	// "FROM li_wallet_detail d " +
	// "LEFT JOIN li_recharge r ON r.recharge_sn = d.sn " +
	// " LEFT JOIN li_transfer t on t.sn = d.sn " +
	// "LEFT JOIN li_member m ON m.id = r.member_id " +
	// " LEFT JOIN li_order o ON o.sn = d.sn " +
	// " LEFT JOIN li_member mm on mm.id = o.member_id " +
	// "LEFT JOIN li_store s ON s.id = o.store_id " +
	// "LEFT JOIN li_grade_level g ON g.member_id = o.member_id AND g.delete_flag =
	// 0 " +
	// "LEFT JOIN li_grade_level gg ON gg.member_id = r.member_id AND gg.delete_flag
	// = 0 " +
	// "LEFT JOIN li_membership_card mc ON mc.id = g.grade_id AND mc.`level` IN ( 1,
	// 2 ) " +
	// "LEFT JOIN li_membership_card mcc ON mcc.id = gg.grade_id AND mcc.`level` IN
	// ( 1, 2 ) ${ew.customSqlSegment}")
	// IPage<WalletDetailVO> findDay(IPage<WalletDetailVO> page, @Param("memberId")
	// String memberId,@Param("format")
	// String format);

	@Select(" SELECT sum(d.money) money " + " FROM li_wallet_detail d "
			+ " LEFT JOIN li_transfer t ON t.sn = d.sn ${ew.customSqlSegment} ")
	Double getCommission(@Param(Constants.WRAPPER) Wrapper<WalletDetail> queryWrapper);

	@Select("SELECT * from ( SELECT t.type as transferType,"
			+ "( case when not ISNULL(m.face) then m.face when not ISNULL(mm.face) then mm.face when not ISNULL(mmt.face) then mmt.face end ) as face,"
			+ "( case when not ISNULL(m.nick_name) then m.nick_name when not ISNULL(mm.nick_name) then mm.nick_name when not ISNULL(mmt.nick_name) then mmt.nick_name end ) as nickName,"
			+ " ( case when not ISNULL(m.mobile) then m.mobile when not ISNULL(mm.mobile) then mm.mobile when not ISNULL(mmt.mobile) then mmt.mobile end ) as mobile, "
			+ "( case when not ISNULL(mc.`name`) then mc.`name` when not ISNULL(mcc.`name`) then mcc.`name` when not ISNULL(mcct.name) then mcct.name end ) as cardName,"
			+ "a.apply_price as realMoney,a.apply_money as applyMoney,o.order_type,LEFT( d.sn, 1 ) AS type,RoundDown(sum(d.sumMoney),2) sumMoney,"
			+ "d.STATUS,d.id,d.create_by,d.create_time,d.transaction_type,d.sn,d.payer,d.payer_name,d.payer_owner,d.payer_before_money,"
			+ "d.payer_after_money,d.payee,d.payee_name,d.payee_owner,d.payee_before_money,d.payee_after_money,d.money,d.remark,"
			+ "d.commission_flag FROM ("
			+ "SELECT IF(payer = #{memberId} and payer_owner = #{owner}, - abs( wd.money ), 0 ) AS sumMoney,"
			+ "IF(payer = #{memberId} and payer_owner = #{owner}, 'fu', 'shou') AS STATUS,wd.*,'' as commission_flag FROM"
			+ " li_wallet_detail wd UNION ALL "
			+ "SELECT IF(payer = #{memberId} and payer_owner = #{owner}, - abs( wd.money ), 0 ) AS sumMoney,"
			+ "IF(payer = #{memberId} and payer_owner = #{owner}, 'fu', 'shou') AS STATUS,wd.* FROM"
			+ " li_wallet_frozen_detail wd WHERE transaction_type = 'WALLET_COMMISSION_FW') d "
			+ " LEFT JOIN li_order o ON o.sn = d.sn " + " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " LEFT JOIN li_member m ON m.id = o.member_id " + " LEFT JOIN li_member_withdraw_apply a ON a.sn = d.sn "
			+ " LEFT JOIN li_recharge r ON r.recharge_sn = d.sn " + " LEFT JOIN li_member mm ON mm.id = r.member_id "
			+ " LEFT JOIN li_member mmt ON mmt.id = t.payer "
			+ " LEFT JOIN li_grade_level g ON g.member_id = o.member_id AND g.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level gg ON gg.member_id = r.member_id AND gg.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level ggt ON ggt.member_id = t.payer AND gg.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card mc ON mc.id = g.grade_id AND mc.`level` IN ( 1, 2 ) "
			+ " LEFT JOIN li_membership_card mcc ON mcc.id = gg.grade_id AND mcc.`level` IN ( 1, 2 ) "
			+ " LEFT JOIN li_membership_card mcct ON mcct.id = ggt.grade_id AND mcc.`level` IN ( 1, 2 )"
			+ " ${ew.customSqlSegment})a where a.sumMoney != 0 ")
	IPage<WalletDetailVO> getByPageOut(IPage<WalletDetailVO> page,
			@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper, @Param("memberId") String memberId,
			@Param("owner") String owner);

	@Select("SELECT * from (  SELECT t.type as transferType,"
			+ "( case when not ISNULL(m.face) then m.face when not ISNULL(mm.face) then mm.face when not ISNULL(mmt.face) then mmt.face end ) as face,"
			+ "( case when not ISNULL(m.nick_name) then m.nick_name when not ISNULL(mm.nick_name) then mm.nick_name when not ISNULL(mmt.nick_name) then mmt.nick_name end ) as nickName,"
			+ " ( case when not ISNULL(m.mobile) then m.mobile when not ISNULL(mm.mobile) then mm.mobile when not ISNULL(mmt.mobile) then mmt.mobile end ) as mobile, "
			+ "( case when not ISNULL(mc.`name`) then mc.`name` when not ISNULL(mcc.`name`) then mcc.`name` when not ISNULL(mcct.name) then mcct.name end ) as cardName,"
			+ "a.apply_price as realMoney,a.apply_money as applyMoney,o.order_type,LEFT( d.sn, 1 ) AS type,RoundDown(sum(d.sumMoney),2) sumMoney,"
			+ "d.STATUS,d.id,d.create_by,d.create_time,d.transaction_type,d.sn,d.payer,d.payer_name,d.payer_owner,d.payer_before_money,"
			+ "d.payer_after_money,d.payee,d.payee_name,d.payee_owner,d.payee_before_money,d.payee_after_money,d.money,d.remark,"
			+ "d.commission_flag FROM ("
			+ "SELECT IF(payee = #{memberId} and payee_owner = #{owner}, wd.money, 0 ) AS sumMoney,"
			+ " IF(payee = #{memberId} and payee_owner = #{owner}, 'shou', 'fu' ) AS STATUS,wd.*,'' as commission_flag FROM"
			+ " li_wallet_detail wd UNION ALL "
			+ "SELECT IF(payee = #{memberId} and payee_owner = #{owner}, wd.money, 0 ) AS sumMoney,"
			+ " IF(payee = #{memberId} and payee_owner = #{owner}, 'shou', 'fu' ) AS STATUS,wd.* FROM"
			+ " li_wallet_frozen_detail wd WHERE transaction_type = 'WALLET_COMMISSION_FW_REFUND' ) d "
			+ " LEFT JOIN li_order o ON o.sn = d.sn " + " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " LEFT JOIN li_member m ON m.id = o.member_id " + " LEFT JOIN li_member_withdraw_apply a ON a.sn = d.sn "
			+ " LEFT JOIN li_recharge r ON r.recharge_sn = d.sn " + " LEFT JOIN li_member mm ON mm.id = r.member_id "
			+ " LEFT JOIN li_member mmt ON mmt.id = t.payer "
			+ " LEFT JOIN li_grade_level g ON g.member_id = o.member_id AND g.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level gg ON gg.member_id = r.member_id AND gg.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level ggt ON ggt.member_id = t.payer AND gg.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card mc ON mc.id = g.grade_id AND mc.`level` IN ( 1, 2 ) "
			+ " LEFT JOIN li_membership_card mcc ON mcc.id = gg.grade_id AND mcc.`level` IN ( 1, 2 ) "
			+ " LEFT JOIN li_membership_card mcct ON mcct.id = ggt.grade_id AND mcc.`level` IN ( 1, 2 )"
			+ " ${ew.customSqlSegment} )a where a.sumMoney != 0")
	IPage<WalletDetailVO> getByPageIn(IPage<WalletDetailVO> page,
			@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper, @Param("memberId") String memberId,
			@Param("owner") String owner);

	@Select(" SELECT * from ( SELECT t.type as transferType,"
			+ "( case when not ISNULL(m.face) then m.face when not ISNULL(mm.face) then mm.face when not ISNULL(mmt.face) then mmt.face end ) as face,"
			+ "( case when not ISNULL(m.nick_name) then m.nick_name when not ISNULL(mm.nick_name) then mm.nick_name when not ISNULL(mmt.nick_name) then mmt.nick_name end ) as nickName,"
			+ " ( case when not ISNULL(m.mobile) then m.mobile when not ISNULL(mm.mobile) then mm.mobile when not ISNULL(mmt.mobile) then mmt.mobile end ) as mobile, "
			+ "( case when not ISNULL(mc.`name`) then mc.`name` when not ISNULL(mcc.`name`) then mcc.`name` when not ISNULL(mcct.name) then mcct.name end ) as cardName,"
			+ "a.apply_price as realMoney,a.apply_money as applyMoney,o.order_type,LEFT( d.sn, 1 ) AS type,RoundDown(sum(d.sumMoney),2) sumMoney,"
			+ "d.STATUS,d.id,d.create_by,d.create_time,d.transaction_type,d.sn,d.payer,d.payer_name,d.payer_owner,d.payer_before_money,"
			+ "d.payer_after_money,d.payee,d.payee_name,d.payee_owner,d.payee_before_money,d.payee_after_money,d.money,d.remark,"
			+ "d.commission_flag FROM ("
			+ "SELECT IF(payer = #{memberId} and payer_owner = #{owner}, - abs( wd.money ), 0 ) AS sumMoney,"
			+ "IF(payer = #{memberId} and payer_owner = #{owner}, 'fu', 'shou') AS STATUS,wd.*,'' as commission_flag FROM"
			+ " li_wallet_detail wd UNION ALL "
			+ "SELECT IF(payee = #{memberId} and payee_owner = #{owner}, wd.money, 0 ) AS sumMoney,"
			+ " IF(payee = #{memberId} and payee_owner = #{owner}, 'shou', 'fu' ) AS STATUS,wd.*,'' as commission_flag FROM"
			+ " li_wallet_detail wd UNION ALL "
			+ "SELECT IF(payer = #{memberId} and payer_owner = #{owner}, - abs( wd.money ), 0 ) AS sumMoney,"
			+ "IF(payer = #{memberId} and payer_owner = #{owner}, 'fu', 'shou') AS STATUS,wd.* FROM"
			+ " li_wallet_frozen_detail wd WHERE transaction_type = 'WALLET_COMMISSION_FW' UNION ALL "
			+ "SELECT IF(payee = #{memberId} and payee_owner = #{owner}, wd.money, 0 ) AS sumMoney,"
			+ " IF(payee = #{memberId} and payee_owner = #{owner}, 'shou', 'fu' ) AS STATUS,wd.* FROM"
			+ " li_wallet_frozen_detail wd WHERE transaction_type = 'WALLET_COMMISSION_FW_REFUND' ) d "
			+ " LEFT JOIN li_order o ON o.sn = d.sn " + " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " LEFT JOIN li_member m ON m.id = o.member_id " + " LEFT JOIN li_member_withdraw_apply a ON a.sn = d.sn "
			+ " LEFT JOIN li_recharge r ON r.recharge_sn = d.sn " + " LEFT JOIN li_member mm ON mm.id = r.member_id "
			+ " LEFT JOIN li_member mmt ON mmt.id = t.payer "
			+ " LEFT JOIN li_grade_level g ON g.member_id = o.member_id AND g.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level gg ON gg.member_id = r.member_id AND gg.delete_flag = 0 "
			+ " LEFT JOIN li_grade_level ggt ON ggt.member_id = t.payer AND gg.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card mc ON mc.id = g.grade_id AND mc.`level` IN ( 1, 2 ) "
			+ " LEFT JOIN li_membership_card mcc ON mcc.id = gg.grade_id AND mcc.`level` IN ( 1, 2 ) "
			+ " LEFT JOIN li_membership_card mcct ON mcct.id = ggt.grade_id AND mcc.`level` IN ( 1, 2 )"
			+ " ${ew.customSqlSegment})a where a.sumMoney != 0 ")
	IPage<WalletDetailVO> getByPageAll(IPage<WalletDetailVO> page,
			@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper, @Param("memberId") String memberId,
			@Param("owner") String owner);

	@Select("SELECT IFNULL(sum(d.money),0) FROM li_wallet_detail d LEFT JOIN li_recharge r on r.recharge_sn = d.sn ${ew.customSqlSegment} ")
	Double getByPageSum(@Param(Constants.WRAPPER) Wrapper<WalletDetail> queryWrapper);

	// 根据sn查询明细
	@Select("SELECT IF(payer = #{memberId}, -abs(d.money) ,d.money) AS sumMoney,"
			+ " IF( payer = #{memberId}, 'fu', 'shou' ) AS STATUS," + " LEFT( d.sn, 1 ) AS type,d.* "
			+ " FROM li_wallet_detail d" + " LEFT JOIN li_order o ON o.sn = d.sn " + " ${ew.customSqlSegment} ")
	List<WalletDetailVO> getListBySnOut(@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper,
			@Param("memberId") String memberId);

	@Select("SELECT IF(payee = #{memberId}, d.money ,-abs(d.money)) AS sumMoney,"
			+ " IF( payee = #{memberId},'shou', 'fu' ) AS STATUS," + " LEFT( d.sn, 1 ) AS type,d.* "
			+ " FROM li_wallet_detail d" + " LEFT JOIN li_order o ON o.sn = d.sn " + " ${ew.customSqlSegment} ")
	List<WalletDetailVO> getListBySnIn(@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper,
			@Param("memberId") String memberId);

	@Select("SELECT * FROM" + "(" + "SELECT"
			+ " IF(payer = #{memberId} and payer_owner = #{owner}, - abs( wd.money ), 0 ) AS sumMoney,"
			+ "IF(payer = #{memberId} and payer_owner = #{owner}, 'fu', 'shou') AS STATUS,"
			+ "LEFT( wd.sn, 1 ) AS type," + "wd.* " + " FROM" + " li_wallet_detail wd"
			+ " LEFT JOIN li_order o ON o.sn = wd.sn UNION ALL " + "SELECT"
			+ " IF(payee = #{memberId} and payee_owner = #{owner}, wd.money, 0 ) AS sumMoney,"
			+ " IF(payee = #{memberId} and payee_owner = #{owner}, 'shou', 'fu' ) AS STATUS,"
			+ " LEFT( wd.sn, 1 ) AS type," + "wd.* " + " FROM" + " li_wallet_detail wd"
			+ " LEFT JOIN li_order o ON o.sn = wd.sn " + ") d ${ew.customSqlSegment} ")
	List<WalletDetailVO> getListBySnAll(@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper,
			@Param("memberId") String memberId, @Param("owner") String owner);

	// 查询明细-admin
	@Select("SELECT m.location as regionId,mc.name as payeeGradeName,m.real_name,m.nick_name,s.store_name,m.mobile,d.* "
			+ " FROM li_wallet_detail d" + " left join li_member m on m.id = d.payee"
			+ " left join li_grade_level g on g.member_id = d.payee and g.delete_flag = 0 and g.grade_state = 0"
			+ " left join li_membership_card mc on mc.id = g.grade_id"
			+ " LEFT JOIN li_store s ON s.member_id = d.payee"
			+ " left join li_order o on o.sn = d.sn ${ew.customSqlSegment}")
	IPage<WalletDetailVO> getListAdminOut(IPage<WalletDetailVO> page,
			@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper);

	// 查询明细-admin
	@Select("SELECT m.location as regionId,mc.name as payeeGradeName,m.real_name,m.nick_name,o.store_name,m.mobile,d.* "
			+ " FROM li_wallet_detail d" + " left join li_member m on m.id = d.payee"
			+ " left join li_grade_level g on g.member_id = d.payee and g.delete_flag = 0 and g.grade_state = 0"
			+ " left join li_membership_card mc on mc.id = g.grade_id" + " left join li_order o on o.sn = d.sn"
			+ " ${ew.customSqlSegment}")
	List<WalletDetailVO> getListAdminOutList(@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper);

	// 查询明细-admin
	@Select("SELECT m.location as regionId,mc.name as payeeGradeName,m.real_name,m.nick_name,s.store_name,m.mobile,d.* "
			+ " FROM li_wallet_detail d" + " left join li_member m on m.id = d.payer"
			+ " left join li_grade_level g on g.member_id = d.payer and g.delete_flag = 0 and g.grade_state = 0"
			+ " left join li_membership_card mc on mc.id = g.grade_id"
			+ " LEFT JOIN li_store s ON s.member_id = d.payer"
			+ " left join li_order o on o.sn = d.sn LEFT JOIN li_recharge lr ON lr.recharge_sn = d.sn and lr.recharge_type !=5 "
			+ " left join li_transfer t on t.sn = d.sn" + " ${ew.customSqlSegment}")
	IPage<WalletDetailVO> getListAdminIn(IPage<WalletDetailVO> page,
			@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper);

	// 查询明细-admin
	@Select("SELECT m.location as regionId,mc.name as payeeGradeName,m.real_name,m.nick_name,o.store_name,m.mobile,d.* "
			+ " FROM li_wallet_detail d" + " left join li_member m on m.id = d.payer"
			+ " left join li_grade_level g on g.member_id = d.payer and g.delete_flag = 0 and g.grade_state = 0"
			+ " left join li_membership_card mc on mc.id = g.grade_id" + " left join li_order o on o.sn = d.sn"
			+ " LEFT JOIN li_recharge lr ON lr.recharge_sn = d.sn and lr.recharge_type !=5 "
			+ " left join li_transfer t on t.sn = d.sn " + " ${ew.customSqlSegment}")
	List<WalletDetailVO> getListAdminInList(@Param(Constants.WRAPPER) Wrapper<WalletDetailVO> queryWrapper);

	// 查询销售钱包明细
	@Select(" SELECT " + " create_time, " + " sn, " + " nick_name, " + " mobile, " + " gradeName, " + " regionId, "
			+ " payer, " + " payee, " + " dingdan, " + " yunfei, " + " fuwufei, "
			+ " dingdan + yunfei + fuwufei as allMoney " + " from ( " + " SELECT " + " a.create_time, " + " a.sn, "
			+ " a.nick_name, " + " a.mobile, " + " a.gradeName, " + " a.regionId, " + " a.payer, " + " a.payee, "
			+ " sum(a.dingdan) dingdan, " + " sum(a.yunfei) yunfei, " + " sum(a.fuwufei) fuwufei " + " from ( "
			+ " SELECT " + " o.create_time, " + " o.sn, " + " m.nick_name, " + " m.mobile, " + " mc.name as gradeName, "
			+ " m.location as regionId, " + " d.payer, " + " d.payer_owner," + " d.payee, " + " d.payee_owner,"
			+ " CASE d.transaction_type " + " WHEN 'WALLET_PAY' THEN d.money " + " WHEN 'WALLET_REFUND' THEN d.money "
			+ " ELSE 0 END as dingdan, " + " CASE d.transaction_type " + " WHEN 'WALLET_COLLECT' THEN d.money "
			+ " WHEN 'WALLET_COLLECT_REFUND' THEN d.money " + " ELSE 0 END as yunfei, " + " CASE d.transaction_type "
			+ " WHEN 'WALLET_COMMISSION_HY' THEN d.money " + " WHEN 'WALLET_COMMISSION_FW' THEN d.money "
			+ " WHEN 'WALLET_COMMISSION_TS' THEN d.money " + " WHEN 'WALLET_COMMISSION_SY' THEN d.money "
			+ " WHEN 'WALLET_COMMISSION_HY_REFUND' THEN d.money " + " WHEN 'WALLET_COMMISSION_FW_REFUND' THEN d.money "
			+ " WHEN 'WALLET_COMMISSION_TS_REFUND' THEN d.money " + " WHEN 'WALLET_COMMISSION_SY_REFUND' THEN d.money "
			+ " ELSE 0 END as fuwufei " + " FROM li_wallet_detail d " + " left join li_order o on o.sn = d.sn "
			+ " left join li_member m on m.id = o.member_id "
			+ " left join li_grade_level gl on m.id = gl.member_id and gl.delete_flag = 0 "
			+ " left join li_membership_card mc on mc.id = gl.grade_id " + " ORDER BY o.payment_time desc )a "
			+ " ${ew.customSqlSegment}" + " )aa")
	IPage<OrderVO> getSaleMoney(IPage<OrderVO> page, @Param(Constants.WRAPPER) QueryWrapper<OrderVO> queryWrapper);

	@Select("select ROUND(sum(money),5) " + " FROM ( " + " SELECT d.money,d.create_time,d.sn "
			+ " FROM li_wallet_detail d " + " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " WHERE d.payee = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_FW','WALLET_COMMISSION_SY',"
			+ "'WALLET_COMMISSION_TS','WALLET_COMMISSION_HY','WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND',"
			+ "'WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) " + " UNION all "
			+ " SELECT -d.money as money, d.create_time,d.sn " + " FROM li_wallet_detail d "
			+ " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " WHERE d.payer = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_SY','WALLET_COMMISSION_TS',"
			+ " 'WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND','WALLET_COMMISSION_FW_REFUND',"
			+ "'WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) )as a")
	Double getCommissionAll(@Param("memberId") String memberId, @Param("format") String format);

	@Select("select ROUND(sum(money),5) " + " FROM ( " + " SELECT d.money,d.create_time,d.sn "
			+ " FROM li_wallet_detail d " + " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " WHERE d.payee = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_FW','WALLET_COMMISSION_SY',"
			+ "'WALLET_COMMISSION_TS','WALLET_COMMISSION_HY','WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND',"
			+ "'WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) AND date_format(d.create_time, '%Y') = #{format} "
			+ " UNION all " + " SELECT -d.money as money, d.create_time,d.sn " + " FROM li_wallet_detail d "
			+ " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " WHERE d.payer = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_SY','WALLET_COMMISSION_TS',"
			+ " 'WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND','WALLET_COMMISSION_FW_REFUND',"
			+ "'WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) AND date_format(d.create_time, '%Y') = #{format} )as a GROUP BY DATE_FORMAT(create_time, '%Y')")
	Double getCommissionYear(@Param("memberId") String memberId, @Param("format") String format);

	@Select("select ROUND(sum(money),5) " + " FROM ( " + " SELECT d.money,d.create_time,d.sn "
			+ " FROM li_wallet_detail d " + " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " WHERE d.payee = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_FW','WALLET_COMMISSION_SY',"
			+ "'WALLET_COMMISSION_TS','WALLET_COMMISSION_HY','WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND',"
			+ "'WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) AND date_format(d.create_time, '%Y-%m') = #{format} "
			+ " UNION all " + " SELECT -d.money as money, d.create_time,d.sn " + " FROM li_wallet_detail d "
			+ " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " WHERE d.payer = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_SY','WALLET_COMMISSION_TS',"
			+ " 'WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND','WALLET_COMMISSION_FW_REFUND',"
			+ "'WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) AND date_format(d.create_time, '%Y-%m') = #{format} )as a GROUP BY DATE_FORMAT(create_time, '%Y-%m')")
	Double getCommissionMonth(@Param("memberId") String memberId, @Param("format") String format);

	@Select("select ROUND(sum(money),5) " + " FROM ( " + " SELECT d.money,d.create_time,d.sn "
			+ " FROM li_wallet_detail d " + " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " WHERE d.payee = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_FW','WALLET_COMMISSION_SY',"
			+ "'WALLET_COMMISSION_TS','WALLET_COMMISSION_HY','WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND',"
			+ "'WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) AND date_format(d.create_time, '%Y-%m-%d') = #{format} "
			+ " UNION all " + " SELECT -d.money as money, d.create_time,d.sn " + " FROM li_wallet_detail d "
			+ " LEFT JOIN li_transfer t ON t.sn = d.sn "
			+ " WHERE d.payer = #{memberId} AND d.transaction_type IN ( 'WALLET_COMMISSION_SY','WALLET_COMMISSION_TS',"
			+ " 'WALLET_COMMISSION_HY_REFUND','WALLET_COMMISSION_TS_REFUND','WALLET_COMMISSION_FW_REFUND',"
			+ "'WALLET_COMMISSION_SY_REFUND','WALLET_COMMISSION','WALLET_COMMISSIONREFUND' ) "
			+ "AND (( t.type <> 'PORT_FEE' ) OR ( t.type IS NULL )) AND date_format(d.create_time, '%Y-%m-%d') = #{format} )as a GROUP BY DATE_FORMAT(create_time,'%Y-%M-%D')")
	Double getCommissionDay(@Param("memberId") String memberId, @Param("format") String format);


	//临时   查询未退款的提现订单
	@Select("SELECT * from li_wallet_detail "
		+ " where sn like 'W%' and transaction_type = 'WALLET_WITHDRAWAL'"
		+ " and sn not in (SELECT sn from li_wallet_detail where sn LIKE 'W%' and transaction_type != 'WALLET_WITHDRAWAL')")
	List<WalletDetail> getUnRefund();


	//临时  查询转账订单
	@Select("SELECT sumMoney,mc.level as cardName,d.* from li_wallet_detail d"
		+ " left join (SELECT sum(money) sumMoney,sn FROM li_wallet_detail "
		+ "WHERE sn LIKE 'Z%' AND transaction_type = 'WALLET_COMMISSION_FW'  GROUP BY sn ) dd on dd.sn = d.sn"
		+ " left join li_grade_level gl on gl.member_id = d.payee"
		+ " left join li_membership_card mc on mc.id = gl.grade_id"
		+ " WHERE d.sn LIKE 'Z%' AND d.transaction_type in ('WALLET_PAY','WALLET_COMMISSION_FW_REFUND' )")
	List<WalletDetailVO> getTransfer();
}
